﻿using Dapper;
using GLstudent.Models;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace GLstudent.DAL
{
    public class DAL_student
    {
        /// <summary>
        /// 按科目查询学生成绩信息
        /// </summary>
        /// <param name="sxuehao">学生学号</param>
        /// <returns></returns>
        public List<chengjishitu> chaxunid(string sxuehao)
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var chaxunid = mySqlConnection.Query<chengjishitu>("SELECT student.b_name AS bname,chengji.id AS chengjiid,student.s_name AS sname,chengji.fenshu AS fenshu , kecheng.k_name AS kname ,teacher.t_name AS tname FROM chengji JOIN student ON chengji.s_xuehao = student.s_xuehao JOIN kecheng ON kecheng.id = chengji.kecheng_id JOIN teacher ON teacher.t_bianhao = chengji.t_bianhao WHERE student.s_xuehao = @sxuehao ", new { sxuehao }).ToList();
            return chaxunid;
        }
        /// <summary>
        /// 模糊查询学生成绩的方法
        /// </summary>
        /// <param name="mohu">模糊查询输入的字符</param>
        /// <param name="sxuehao">学生学号</param>
        /// <returns></returns>
        public List<chengjishitu> mohuchengji(string mohu,string sxuehao)
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var list = mySqlConnection.Query<chengjishitu>
                ($"SELECT student.b_name AS bname,chengji.id AS chengjiid,student.s_name AS sname,chengji.fenshu AS fenshu , kecheng.k_name AS kname ,teacher.t_name AS tname FROM chengji JOIN student ON chengji.s_xuehao = student.s_xuehao JOIN kecheng ON kecheng.id = chengji.kecheng_id JOIN teacher ON teacher.t_bianhao = chengji.t_bianhao WHERE student.s_xuehao = @sxuehao AND kecheng.k_name LIKE  '%{mohu}%' ", new { mohu,sxuehao }).ToList();
            return list;
        }
        /// <summary>
        /// 按ID查询学生成绩信息
        /// </summary>
        /// <param name="id">要查询的id</param>
        /// <returns></returns>
        public chengjishitu chaxunkechengid(int id)
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var chaxunid = mySqlConnection.QueryFirstOrDefault<chengjishitu>("SELECT student.b_name AS bname,chengji.id AS chengjiid,student.s_name AS sname,chengji.fenshu AS fenshu , kecheng.k_name AS kname ,teacher.t_name AS tname FROM chengji JOIN student ON chengji.s_xuehao = student.s_xuehao JOIN kecheng ON kecheng.id = chengji.kecheng_id JOIN teacher ON teacher.t_bianhao = chengji.t_bianhao WHERE chengji.id = @id  ", new { id });
            return chaxunid;
        }
    }
}
